﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmFinancialBudget

    Sub FillBudget()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridBudget.Rows.Clear()

            Dim cmd As New SqlCommand("Select * From Budget Where Branch=N'" & Me.CombBranch.SelectedItem & _
                                      "' and Year=" & Me.DTPYear.Value.Year, cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridBudget.Rows.Add(New String() {Reader.Item("AccNo"), Reader.Item("Acc1"), Reader.Item("Acc2"), Reader.Item("Acc3"), _
                                                     Reader.Item("Acc4"), "Search....", CDbl(Reader.Item("Amount")).ToString("N2"), "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombBranch.Items.Clear()
            Me.CombBranchRpt.Items.Clear()

            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
                Me.CombBranchRpt.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmBudget_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.ErrProv.Clear()
        Try
            If Me.RBranch.Checked = True And Me.CombBranchRpt.SelectedIndex = -1 Then
                Me.ErrProv.SetError(Me.CombBranchRpt, "Please select a valid branch from the list")
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select Distinct AccNo,Acc1,Acc4 From Budget Where Year=" & Me.DTPPeriodFRm.Value.Year, cnn)
                Dim Reader As SqlDataReader
                Dim das As New DsBudget

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    das.Tables(0).Rows.Add(New String() {Reader.Item(1), Reader.Item(2), _
                                                         GetBudget(Reader.Item(0)), GetActualBalance(Reader.Item(0))})
                End While
                cnn.Close()

                If Me.RNumbers.Checked = True Then
                    ShowBudgetNumbers(das)

                ElseIf Me.RChart.Checked = True Then
                    ShowBudgetChart(das)
                End If

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowBudgetNumbers(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New Budget
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranchRpt.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub ShowBudgetChart(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New BudgetChart
            rpt.SetDataSource(das)

            'Cr Parameter

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranchRpt.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Function GetBudget(ByVal AccNo As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim condBranch As String
            If Me.RBranch.Checked = True Then
                condBranch = " and Branch=N'" & Me.CombBranchRpt.SelectedItem & "'"
            Else
                condBranch = ""
            End If

            Dim cmd As New SqlCommand("Select IsNull(Sum(Amount),0) From Budget Where Year=" & Me.DTPPeriodFRm.Value.Year & _
                                      " and AccNo=N'" & AccNo & "'" & condBranch, cnn4)
            Dim Budget As Double

            cnn4.Open()
            Budget = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            'Calcualte amount vs. no. of months selected in period
            Dim Months As Integer = DateDiff(DateInterval.Month, Me.DTPPeriodFRm.Value, Me.DTPPeriodTo.Value) + 1
            Budget = Months * (Budget / 12)

            Me.Cursor = Cursors.Default

            Return Budget
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Function GetActualBalance(ByVal AccNo As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim condBranch As String
            If Me.RBranch.Checked = True Then
                condBranch = " and Branch=N'" & Me.CombBranchRpt.SelectedItem & "'"
            Else
                condBranch = ""
            End If

            Dim cmd As New SqlCommand("Select IsNull(Sum(TotalIn)-Sum(TotalOut),0) From Transactions Where AccNo=N'" & AccNo & "' and " & _
                                      "Year(TransDate)=" & Me.DTPPeriodFRm.Value.Year & condBranch & _
                                      " and Month(TransDate) Between " & Me.DTPPeriodFRm.Value.Month & " and " & Me.DTPPeriodTo.Value.Month, cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            If Balance < 0 Then
                Balance = Balance * -1
            End If
            Me.Cursor = Cursors.Default

            Return Balance
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Private Sub ChAccPath_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChAccPath.CheckedChanged
        If Me.ChAccPath.Checked = True Then
            Me.Acc1.Visible = True
            Me.Acc2.Visible = True
            Me.Acc3.Visible = True
        ElseIf Me.ChAccPath.Checked = False Then
            Me.Acc1.Visible = False
            Me.Acc2.Visible = False
            Me.Acc3.Visible = False
        End If
    End Sub

    Private Sub GridVoucher_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridBudget.CellClick
        If e.ColumnIndex = 5 Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(SelAccNo)

                    Me.GridBudget.CurrentRow.Cells(0).Value = SelAccNo
                    Me.GridBudget.CurrentRow.Cells(1).Value = AccNames(0)
                    Me.GridBudget.CurrentRow.Cells(2).Value = AccNames(1)
                    Me.GridBudget.CurrentRow.Cells(3).Value = AccNames(2)
                    Me.GridBudget.CurrentRow.Cells(4).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        ElseIf e.ColumnIndex = 7 Then
            If e.RowIndex < (Me.GridBudget.Rows.Count - 1) Then
                Me.GridBudget.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub GridVoucher_CellEndEdit(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridBudget.CellEndEdit
        If e.ColumnIndex = 0 Then
            Try
                If Me.GridBudget.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(Me.GridBudget.CurrentCell.Value)

                    Me.GridBudget.CurrentRow.Cells(1).Value = AccNames(0)
                    Me.GridBudget.CurrentRow.Cells(2).Value = AccNames(1)
                    Me.GridBudget.CurrentRow.Cells(3).Value = AccNames(2)
                    Me.GridBudget.CurrentRow.Cells(4).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridBudget.CurrentRow.Cells(1).Value = ""
                    Me.GridBudget.CurrentRow.Cells(2).Value = ""
                    Me.GridBudget.CurrentRow.Cells(3).Value = ""
                    Me.GridBudget.CurrentRow.Cells(4).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        ElseIf e.ColumnIndex = 6 Then
            Try
                Me.GridBudget.CurrentCell.Value = CDbl(Me.GridBudget.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridBudget.CurrentCell.Value = "0.00"
            End Try
        End If

        ValidateGrid()
    End Sub

    Sub ValidateGrid()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridBudget.Rows
                If row.Cells(4).Value = "" Then
                    row.Cells(4).Style.BackColor = Color.Salmon
                Else
                    row.Cells(4).Style.BackColor = row.DefaultCellStyle.BackColor
                End If

                If CDbl(row.Cells(6).Value) = 0 Then
                    row.Cells(6).Style.BackColor = Color.Salmon
                Else
                    row.Cells(6).Style.BackColor = row.DefaultCellStyle.BackColor
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        ErrorProvider1.Clear()
        If Me.CombBranch.SelectedIndex = -1 Then
            ErrorProvider1.SetError(Me.CombBranch, "Please select a valid branch")

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Delete old records
                cmd.CommandText = "Delete From Budget Where Branch=N'" & Me.CombBranch.SelectedItem & "' and Year=" & Me.DTPYear.Value.Year
                cmd.ExecuteNonQuery()

                'Save new records
                For Each row As DataGridViewRow In Me.GridBudget.Rows
                    If row.Index <> (Me.GridBudget.Rows.Count - 1) Then
                        cmd.CommandText = "Insert Into Budget (Branch,Year,AccNo,Acc1,Acc2,Acc3,Acc4,Amount,UserName)" & _
                                          "Values (@Branch,@Year,@AccNo,@Acc1,@Acc2,@Acc3,@Acc4,@Amount,@UserName)"

                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@Branch", Me.CombBranch.Text)
                        cmd.Parameters.AddWithValue("@Year", Me.DTPYear.Value.Year)
                        cmd.Parameters.AddWithValue("@AccNo", row.Cells(0).Value)
                        cmd.Parameters.AddWithValue("@Acc1", row.Cells(1).Value)
                        cmd.Parameters.AddWithValue("@Acc2", row.Cells(2).Value)
                        cmd.Parameters.AddWithValue("@Acc3", row.Cells(3).Value)
                        cmd.Parameters.AddWithValue("@Acc4", row.Cells(4).Value)
                        cmd.Parameters.AddWithValue("@Amount", CDbl(row.Cells(6).Value))
                        cmd.Parameters.AddWithValue("@UserName", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next
                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                FillBudget()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub CombBranch_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles CombBranch.SelectedIndexChanged
        If Me.CombBranch.SelectedIndex = -1 Then
            Me.GridBudget.Rows.Clear()
        Else
            FillBudget()
        End If
    End Sub

    Private Sub DTPYear_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPYear.ValueChanged
        If Me.CombBranch.SelectedIndex = -1 Then
            Me.GridBudget.Rows.Clear()
        Else
            FillBudget()
        End If
    End Sub

    Private Sub RBranch_CheckedChanged_1(sender As System.Object, e As System.EventArgs) Handles RBranch.CheckedChanged
        If Me.RBranch.Checked = True Then
            Me.CombBranchRpt.Enabled = True

        ElseIf Me.RAll.Checked = True Then
            Me.CombBranchRpt.Enabled = False

        End If
    End Sub

    Private Sub DTPPeriodFRm_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPPeriodFRm.ValueChanged
        Me.DTPPeriodTo.Value = New Date(Me.DTPPeriodFRm.Value.Year, Me.DTPPeriodTo.Value.Month, Me.DTPPeriodTo.Value.Day)
    End Sub

    Private Sub DTPPeriodTo_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPPeriodTo.ValueChanged
        Me.DTPPeriodFRm.Value = New Date(Me.DTPPeriodTo.Value.Year, Me.DTPPeriodFRm.Value.Month, Me.DTPPeriodFRm.Value.Day)
    End Sub
End Class